using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Mono.Data.SqliteClient;
using System.IO;

namespace Pristine
{
        class MainClass
        {

        //Método que irá mostrar numero de docentes por ano - 1
        static void count_teachers(int ano, string file){
            
            List<string> totais = new List<string>();
            string connectionString = "URI=file:pristine.db,version=3";           
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
			
            string sql = "SELECT COUNT(DISTINCT docente_id) FROM informatica_registodocencia WHERE ano = " + ano;
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);
            while(reader.Read())
            {
                string total = reader.GetString(0);
                totais.Add(total);
            }
            totais.Sort();
            foreach (string tot in totais)
            {                           
                Console.WriteLine("Total number of teachers in the higher education system in the year 200{0} - {1}", ano, tot);
                sw.WriteLine("Total number of teachers in the higher education system in the year 200{0} - {1}", ano, tot);
            }
            sw.Close();
            reader.Close();
            reader = null;
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
        }
        
                //Numero de docentes por estabelecimento e por ano -2
        static void count_teachers_Year_estab(int ano, string estab, string file)
        {
            List<string> totais = new List<string>();
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT COUNT(DISTINCT docente_id) " +
            			 "FROM informatica_registodocencia " +
            			 "WHERE ano = " + ano +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '"+ estab +"' )";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);
            while (reader.Read())
            {
                string total = reader.GetString(0);
                totais.Add(total);
            }
            totais.Sort();
            foreach (string tot in totais)
            {
                Console.WriteLine("Total number of teachers in establishment {0} and year 200{1} - {2}", estab, ano, tot);
                sw.WriteLine("Total number of teachers in establishment {0} and year 200{1} - {2}", estab, ano, tot);
            }
            
            sw.Close();

            reader.Close();
            reader = null;
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
        }
        //Numero de docentes por grau e por ano -3
        static void count_teachers_degree(int ano, string grau, string file)
        {
            List<string> totais = new List<string>();
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT COUNT(DISTINCT docente_id) " +
            	"FROM informatica_registodocencia " +
            	"WHERE ano = " + ano +
                " AND grau_id= (SELECT id FROM informatica_grau WHERE designacao= '" + grau + "' )";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);
            while (reader.Read())
            {
                string total = reader.GetString(0);
                totais.Add(total);
            }
            totais.Sort();
            
            foreach (string tot in totais)
            {
                Console.WriteLine("Total number of teachers in degree {0} and year 200{1} - {2}", grau, ano, tot);
                sw.WriteLine("Total number of teachers in degree {0} and year 200{1} - {2}", grau, ano, tot);
            }

            sw.Close();

            reader.Close();
            reader = null;
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
        }
        //Numero de docentes por estabelecimento, ano e grau - 4
        static void count_teachers_Year_degree_estab(int ano, string grau, string estab, string file)
        {
            List<string> totais = new List<string>();
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand(); 
            string sql = "SELECT COUNT(DISTINCT docente_id) " +
            	         "FROM informatica_registodocencia WHERE ano = " + ano +
                         " AND estabelecimento_id= " +
                         "(SELECT id FROM informatica_estabelecimento " +
                         "WHERE designacao= '"+ estab +"' )" +
                         " AND grau_id= " +
                         "(SELECT id FROM informatica_grau " +
                         "WHERE designacao= '" + grau + "' )";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);
            while (reader.Read())
            {
                string total = reader.GetString(0);
                totais.Add(total);
            }
            totais.Sort();
            foreach (string tot in totais)
            {
                Console.WriteLine("Total number of teachers in degree {0}, in establishment {1} and year 200{2} - {3}", grau, estab, ano, tot);
                sw.WriteLine("Total number of teachers in degree {0}, in establishment {1} and year 200{2} - {3}", grau, estab, ano, tot);
            }
            sw.Close();
            reader.Close();
            reader = null;
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
        }
        //Numero de doutorados por estabelecimento e por ano -5
        static void Doctoratecount_teachers_Year_estab(int ano, string estab, string file)
        {
            List<string> totais = new List<string>();
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT COUNT(DISTINCT docente_id) FROM informatica_registodocencia WHERE ano = " + ano +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '" + estab + "' )" +
                         " AND grau_id= (SELECT id FROM informatica_grau WHERE ciclo_estudos_id= 3)";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);
            while (reader.Read())
            {
                string total = reader.GetString(0);
                totais.Add(total);
            }
            totais.Sort();
            
            foreach (string num in totais) 
            {
                Console.WriteLine("The number of holders of a doctorate degree, in establishment {0} and year 200{1} - {2}", estab, ano, num);
               
                sw.WriteLine("The number of holders of a doctorate degree, in establishment {0} and year 200{1} - {2}", estab, ano, num);
            }
            
            sw.Close();
         
            reader.Close();
            reader = null;
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
        }
        
        
                //Este método irá mostrar os docentes que têm doutoramento que irá ser usado no metodo seguinte
        static void DoutoramentoID(int ano, string estab, List<string> id)
        {
           
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT docente_id " +
            			 "FROM informatica_registodocencia " +
            			 "WHERE ano = " + ano +
                         " AND estabelecimento_id= " +
                         "(SELECT id FROM informatica_estabelecimento " +
                         "WHERE designacao= '"+ estab +
                         "') AND grau_id= (SELECT id FROM informatica_grau WHERE ciclo_estudos_id= 3)";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
               string n = reader.GetString(0);
               id.Add(n);
            }
        }

        //Lista de doutorados, que usa o método anterior - 6
        static void ListDoctoratecount_teachers_Year_estab(int ano, string estab, string file) 
        {
            
            List<string> lista = new List<string>();
            List<string> id = new List<string>();
            DoutoramentoID(ano, estab, id);
            StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);
            foreach (string complete in id)
            {
                string connectionString = "URI=file:pristine.db,version=3";
                IDbConnection dbcon;
                dbcon = (IDbConnection)new SqliteConnection(connectionString);
                dbcon.Open();
                IDbCommand dbcmd = dbcon.CreateCommand();
                string sql = "SELECT nome FROM informatica_docente WHERE id =" + complete;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();
 
                while (reader.Read())
                {
                    string n = reader.GetString(0);
                    lista.Add(n);
                }

               
                reader.Close();
                reader = null;
                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            }
            
            lista.Sort();
            sw.WriteLine("The set of holders of a doctorate degree in establishment {0} and year 200{1}: ", estab, ano);
            foreach (string listas in lista) 
            {
                Console.WriteLine(listas);
            }
            sw.WriteLine("The set of holders of a doctorate degree in establishment {0} and year 200{1}: ", estab, ano);
            foreach (string listas in lista) 
            {
                sw.WriteLine(listas);
            }
            sw.Close();
        }
        
                //este método dá o id dos docentes por estabelecimento e por ano para usar no metodo seguinte
        static void ID(int ano, string estab, List<string> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano = " + ano +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '" + estab +"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                string n = reader.GetString(0);
                id.Add(n);
            }
        }


       
                 //Lista os docentes de um estabelecimento por ano com a ajuda do metodo anterior
        static void ListTeachers(int ano, string estab, List<string> list)
        {

            List<string> id = new List<string>();
            ID(ano, estab, id);
            foreach (string complete in id)
            {
                string connectionString = "URI=file:pristine.db,version=3";
                IDbConnection dbcon;
                dbcon = (IDbConnection)new SqliteConnection(connectionString);
                dbcon.Open();
                IDbCommand dbcmd = dbcon.CreateCommand();
                string sql = "SELECT nome FROM informatica_docente WHERE id =" + complete;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();

                while (reader.Read())
                {
                    string n = reader.GetString(0);
                    list.Add(n);
                }
                reader.Close();
                reader = null;
                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            }
            list.Sort();
        }

        //lista com os coentes que abandonaram o um estabelecimento e foram para outro - 7
                 
        static void ListTeachers_leave_Estab_year(int ano, string estab1, string estab2, string file) {
            List<string> list = new List<string>();
            List<string> list1 = new List<string>();
            List<string> list2 = new List<string>();
            ListTeachers(ano, estab1, list1);
            ListTeachers(ano, estab2, list2);

            foreach (string teachers1 in list1)
            {
                foreach (string teachers2 in list2)
                {
                    if (teachers1 == teachers2)
                    {
                        list.Add(teachers1);
                    }else{
                                                continue;
                                        }
                }
            }
            list.Sort();
            StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);
            Console.WriteLine("The set of teachers that changed from establishment {0} to establishment {1} in year {2}: ", estab1,estab2, ano);
            foreach (string listas in list)
            {
                Console.WriteLine(listas);
            }
            sw.WriteLine("The set of teachers that changed from establishment {0} to establishment {1} in year {2}: ", estab1, estab2, ano);
            foreach (string listas in list) 
            {
                sw.WriteLine(listas);
            }
            sw.Close();
        }
        
        
		//lista estbelecimento por ano -8
        static void ListEstablismentPerYear(int ano, string file)
        {

            List<string> list = new List<string>();
            StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT designacao FROM informatica_estabelecimento WHERE ano =" + ano;
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
               string n = reader.GetString(0);
               list.Add(n);
            }

                
                reader.Close();
                reader = null;
                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            
            
            list.Sort();
            Console.WriteLine("List of establishments in year 200{0}: ", ano);
            foreach (string listas in list)
            {
                Console.WriteLine(listas);
            }
            
            sw.WriteLine("List of establishments in year 200{0}: ", ano);
            foreach (string listas in list)
            {
                sw.WriteLine(listas);
            }
            sw.Close();

        }
       
       
        //lista os docentes por grau e ano
        static void IDDocentesGrauPorAno(int ano,string grau, List<string> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano = " + ano +
                         " AND grau_id= (SELECT id FROM informatica_grau WHERE designacao='"+grau+"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                string n = reader.GetString(0);
                id.Add(n);
            }
        }


		//lista de titulares de um grau por ano - 9
        static void ListHolderDegree(int ano, string grau,string file)
        {


            List<string> list = new List<string>();
            List<string> id = new List<string>();
            IDDocentesGrauPorAno(ano,grau,id);
            StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);
            foreach (string complete in id)
            {
                string connectionString = "URI=file:pristine.db,version=3";
                IDbConnection dbcon;
                dbcon = (IDbConnection)new SqliteConnection(connectionString);
                dbcon.Open();
                IDbCommand dbcmd = dbcon.CreateCommand();
                string sql = "SELECT nome FROM informatica_docente WHERE id =" + complete;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();

                while (reader.Read())
                {
                    string n = reader.GetString(0);
                    list.Add(n);
                }

                reader.Close();
                reader = null;
                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            }
            list.Sort();
            Console.WriteLine("List of holders of a degree in year {0}: ", ano);
            foreach (string listas in list) 
            {
                Console.WriteLine(listas);
            }
            sw.WriteLine("List of holders of a degree in year {0}: ", ano);
            foreach (string listas in list)
            {
                sw.WriteLine(listas);
            }
          
            sw.Close();

        }

        
        
        //metodo que mostra o docentes por estabelecimento e por ano
        static void TotalDocentes(int ano, string estab, List<int> nr)
        {
            
            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT COUNT(DISTINCT docente_id) FROM informatica_registodocencia WHERE ano = " + ano +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '" + estab +"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
               int numero = reader.GetInt32(0);
               nr.Add(numero);
            }
        }
        /*ID's DE DOCENTES DE UM ESTABELECIMENTO E ANO PARA UTILIZAR NO METODO - ListPersonnelLeaveOrEntry*/
           
        static void IDDocentesLeaveOrEntry(int ano, string estab, List<string> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano = " + ano +
                         " AND estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '" + estab +"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
               string nr = reader.GetString(0);
               id.Add(nr);
            }
        }


        //LISTA DE PESSOAL QUE ABANDONOU INSTITUICAO OU PESSOAL NOVO NA INSTITUICAO - 10
                 
        static void Listinout(int ano, string estab ,string fileLeave , string fileEntry)
        {
            List<int> actuallist = new List<int>();
            List<int> nextlist = new List<int>();
            List<string> list = new List<string>();
            List<string> id = new List<string>();
            IDDocentesLeaveOrEntry(ano, estab, id);
            StreamWriter swEntry = new StreamWriter(fileEntry, true, Encoding.UTF8);
            StreamWriter swLeave = new StreamWriter(fileLeave, true, Encoding.UTF8);
            foreach (string complete in id)
            {
               string connectionString = "URI=file:pristine.db,version=3";
               IDbConnection dbcon;
               dbcon = (IDbConnection)new SqliteConnection(connectionString);
               dbcon.Open();
               IDbCommand dbcmd = dbcon.CreateCommand();
               string sql = "SELECT nome FROM informatica_docente WHERE id =" + complete;
               dbcmd.CommandText = sql;
               IDataReader reader = dbcmd.ExecuteReader();

               while (reader.Read())
               {
                 string n = reader.GetString(0);
                 list.Add(n);
               }
              
               reader.Close();
               reader = null;
               dbcmd.Dispose();
               dbcmd = null;
               dbcon.Close();
               dbcon = null;
                
            }
            list.Sort();
            int diference;
            int total1;
            int total2;

            TotalDocentes(ano, estab, actuallist);
            TotalDocentes((ano+1), estab, nextlist);
            foreach (int actual in actuallist)
            {
                total1 = actual;
                        
              foreach (int next in nextlist)
              {
                total2 = next;         
                diference = total1 - total2;
                if (diference < 0)
                {
                    Console.WriteLine("List of new personnel in the institution {0} in year 200{1}: ", estab, ano);
                    foreach (string listas in list) 
                    {
                        Console.WriteLine(listas);   
                    }
                    swEntry.WriteLine("List of new personnel in the institution {0} in year 200{1}: ", estab, ano); 
                    foreach (string listas in list) 
                    {
                       swEntry.WriteLine(listas);
                    }
                }
                else if (diference > 0)
                {
                    Console.WriteLine("List of the personnel leaving the institution {0} in year 200{1} to year 200{2}: ", estab, ano, ano + 1);
                    foreach (string listas in list) 
                    {
                        Console.WriteLine(listas);
                    }

                    swLeave.WriteLine("List of the personnel leaving the institution {0} in year 200{1} to year 200{2}: ", estab, ano, ano + 1); ;
                    foreach (string listas in list)
                    {
                        swLeave.WriteLine(listas);
                    }

                }
                else{
                    Console.WriteLine("Without alteration the institution {0} in year 200{1} to year 200{2}", estab, ano, ano + 1);
                }
                swEntry.Close();
                swLeave.Close();
               }
            }
        }
        
        // ID DOS DOCENTES NUM ESTABELECIMENTO PARA UTILIZAR NO METODO - ListTeachersToCompare
                
        static void IDNrDocentes(string estab, List<string> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE estabelecimento_id= (SELECT id FROM informatica_estabelecimento WHERE designacao= '" + estab + "')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                string n = reader.GetString(0);
                id.Add(n);
            }
        }


        //LISTA DOCENTES DE UM ESTABELECIMENTO PARA UTILIZAR NO METODO - NumberTeachersMigrateEstablishment
                 
        static void ListTeachersToCompare(string estab, List<string> list)
        {

            List<string> id = new List<string>();
            IDNrDocentes(estab, id);
            foreach (string complete in id)
            {
                string connectionString = "URI=file:pristine.db,version=3";
                IDbConnection dbcon;
                dbcon = (IDbConnection)new SqliteConnection(connectionString);
                dbcon.Open();
                IDbCommand dbcmd = dbcon.CreateCommand();
                string sql = "SELECT nome FROM informatica_docente WHERE id =" + complete;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();

                while (reader.Read())
                {
                    string n = reader.GetString(0);
                    list.Add(n);
                }

                
                reader.Close();
                reader = null;
                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            }
           
            list.Sort();
        }

        //NUMERO DE PROFESSORES QUE MIGRARAM DE UM ESTABELECIMENTO PARA OUTRO 11
                 
        static void NumberInAnother( string estab1, string estab2, string file)
        {
            List<int> list = new List<int>();
            List<string> list1 = new List<string>();
            List<string> list2 = new List<string>();
            ListTeachersToCompare(estab1, list1);
            ListTeachersToCompare(estab2, list2);
            int i=0;
            foreach (string teachers1 in list1)
            {
                foreach (string teachers2 in list2)
                {
                    if (teachers1 == teachers2)
                    {
                        i++;
                    }else{
                                                continue;
                                        }
                }
            }
            list.Add(i);
            StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);

            Console.WriteLine("Number of teachers migrating from establishment {0} to establishment {1}: ", estab1, estab2);
            foreach (int listas in list) 
            {
                Console.WriteLine(listas);
            }
            
            sw.WriteLine("Number of teachers migrating from establishment {0} to establishment {1}: ", estab1, estab2);
            foreach (int listas in list) 
            {
                sw.WriteLine(listas);
            }
            
            sw.Close();
        }

       
                ///ID DOS DOCENTES NUM ANO E UM ESTABELECIMENTO PARA UTILIZAR NO METODO - NumberTeachersPromoteCategoryPerEstablisment
               
        static void IDDocente(int ano, string estab, List<int> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT docente_id FROM informatica_registodocencia WHERE ano =" +ano+ " AND estabelecimento_id= (select id from informatica_estabelecimento where designacao='"+estab+"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
            {
                int docente = reader.GetInt32(0);
                id.Add(docente);
            }
        }
                //ID DAS CATEGORIA NUM ANO E UM ESTABELECIMENTO PARA UTILIZAR NO METODO - NumberTeachersPromoteCategoryPerEstablisment
                 
                static void IDCategoria(int ano, string estab, List<int> id)
        {

            string connectionString = "URI=file:pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();
            string sql = "SELECT categoria_id FROM informatica_registodocencia WHERE ano =" +ano+ " AND estabelecimento_id= (select id from informatica_estabelecimento where designacao='"+estab+"')";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();
            while (reader.Read())
                        {
                                int categoria = reader.GetInt32(0);
                id.Add(categoria);
            }
        }


                /*NUMERO DE PROFESSORES QUE FORAM PROMOVIDOS PARA PROXIMA CATEGORIA 12
                 
                 */
        static void NumberTeachersPromoted(int ano, string estab, string file)
        {
                List<int> actualDocente = new List<int>();
                                List<int> actualCategoria = new List<int>();
                                List<int> nextDocente = new List<int>();
                                List<int> nextCategoria = new List<int>();
                List<int> list = new List<int>();
                IDDocente(ano, estab,actualDocente);
                                IDDocente(ano+1, estab,nextDocente);
                            IDCategoria(ano, estab,actualCategoria);
                                IDCategoria(ano+1, estab,nextCategoria);
                                int count =0;
                foreach(int actualDoce in actualDocente)
                {
                                        foreach(int nextDoce in nextDocente)
                                        {

                                                if(actualDoce == nextDoce)
                                                {
                                                        foreach(int actualCate in actualCategoria)
                                        {
                                                                foreach(int nextCate in nextCategoria)
                                                                {
                                                                        if(actualCate == nextCate)
                                                                        {
                                                                                continue;
                                                                        }else{
                                                                                count++;
                                                                        }
                                                                }
                                                        }
                                                }else{
                                                        continue;
                                                }
                                        }
 
                                }
                                list.Add(count);
                                StreamWriter sw = new StreamWriter(file, true, Encoding.UTF8);
                Console.WriteLine("Number of teachers promoted to the next category in the year 200{0} and in establishment {1}:", ano, estab);
                foreach (int listas in list) 
                {
                        Console.WriteLine(listas);
                }
               
                                sw.WriteLine("Number of teachers promoted to the next category in the year 200{0} and in establishment {1}:",  ano, estab);
                foreach (int listas in list)
                {
                        sw.WriteLine(listas);
                        }
                
                sw.Close();

            
        
        }
     
        

        static void Main(string[] args)
        {
                        
			string continuar;
            do{
			
            Console.WriteLine(
                
                        "1 - Total number of teachers in the higher education system per year\n" +
                        "2 - Total number of teachers per establishment and per year\n" +
                        "3 - Total number of teachers per degree and per year\n" +
                        "4 - Total number of teachers per degree, per establishment and per year\n" +
                        "5 - The number of holders of a doctorate degree(3) per establishment and per year\n" +
                        "6 - Number of teachers migrating from one establishment to another\n" +
                        "7 - Number of teachers promoted to the next category each year per establishment\n" +
                        "8 - List set of holders of a doctorate degree per establishment and per year\n" +
                        "9 - List of set of teachers that changed from one establishment to another one per year \n" +
                        "10 - List of establishments per year\n" +
                        "11 - List of holders of a degree per year\n" +
                        "12 - Personnel leaving the institution and new personnel in the institution per year \n");
                        
                Console.WriteLine("Choose the option:");
                string op = Console.ReadLine();
                int option = int.Parse(op);

                switch (option)
                {
				case 1:
						Console.Write("1 - Total number of teachers in the higher education system per year:\n");
                        for (int i = 0; i < 10; i++)
                        {
                            count_teachers(i, "StatisticTeacherPerYear.txt");
                        }
                        break;
                    case 2:
                        Console.Write("2 - Total number of teachers per establishment and per year\n");
                        count_teachers_Year_estab(7, "Universidade do Minho", "StatisticTeacherPerEstablishmentPerYear.txt");
                        break;
                    case 3:
					Console.Write("3 - Total number of teachers per degree and per year\n");
                        count_teachers_degree(7,"Doutoramento", "StatisticTeacherPerDegreePerYear.txt");
                        break;
                    case 4:
                        Console.Write("4 - Total number of teachers per degree, per establishment and per year\n");
                        count_teachers_Year_degree_estab(7, "Licenciatura", "Universidade do Minho", "StatisticTeacherPerDegreePerEstablishmentPerYear.txt");
                        break;
                    case 5:
                        Console.WriteLine("5 - The number of holders of a doctorate degree(3) per establishment and per year\n");
                        Doctoratecount_teachers_Year_estab(7, "Universidade do Minho", "StatisticDoctoratePerEstablishmentPerYear.txt");
                        break;
                    case 6:
					Console.Write("6 - Number of teachers migrating from one establishment to another\n");
                        NumberInAnother("Universidade do Minho", "Universidade do Porto", "StatisticNumberTeachersMigrateToEstablishment.txt");
                        break;
                    case 7:
					Console.Write("7 - Number of teachers promoted to the next category each year per establishment\n");
                                        for (int i = 0; i < 10; i++)
                        {
                        NumberTeachersPromoted(i, "Universidade de Évora", "StatisticNumberTeachersPromoteNextCategory.txt");
                                                }
                                                break;
                    case 8:
					Console.Write("8 - List set of holders of a doctorate degree per establishment and per year\n");
                        for (int i = 0; i < 10; i++)
                        {
                            ListDoctoratecount_teachers_Year_estab(i, "Universidade do Minho", "ListDoctoratePerEstablishmentPerYear.txt");
                        }
                        break;
                    case 9:
					Console.Write("9 - List of set of teachers that changed from one establishment to another one per year \n");
                        for (int i = 0; i < 10; i++)
                        {
                            ListTeachers_leave_Estab_year(i, "Universidade do Minho", "Universidade do Porto", "ListTeachersChangeEstablishmentPerYear.txt");
                        }
                        break;
                    case 10:
					Console.Write("10 - List of establishments per year\n");
                        for (int i = 0; i < 10; i++)
                        {
                            ListEstablismentPerYear(i, "ListEstablishmentPerYear.txt");
                        }
                        break;
                    case 11:
                        Console.Write("11 - List of holders of a degree per year\n");
                        for (int i=0; i < 10; i++)
                        {
                            ListHolderDegree(i,"Doutoramento", "ListHoldersDegreePerYear.txt");
                        }
                        break;
                    case 12:
                        Console.WriteLine("12 - Personnel leaving the institution and new personnel in the institution per year \n");
                        
                        for (int i = 0; i < 9; i++)
                        {
                            Listinout(i, "Universidade do Minho", "ListPersonnelLeaving.txt", "ListPersonnelEntry.txt");
                        }
                        break;
                    default:
                        Console.WriteLine("Não existe essa opção");
                        break;

                }
                                
								Console.Write("Quer realizar outra operação? (sim/nao) ");
								continuar = Console.ReadLine();
				
			} while(continuar == "sim");
				
                Console.ReadKey();
        }
        }
}

